% Autore: Mattia Coccato
% Argomenti: codice di creazione delle relazione


\chapter{Codice di creazione SQL}
\label{appendiceSQL}

Si riporta nel seguito il codice SQL completo per la costruzione dello schema di base di dati. A quanto qui riportato si devono aggiungere i trigger definiti nel capitolo \ref{chTrigger}.

\lstset{language=SQL, keywordstyle=\color{blue}\bfseries, stringstyle=\color{javared}, commentstyle=\color{javadocblue}}
\begin{lstlisting}[label=sql-totale, caption=Codice complessivo di definizione dello schema di base di dati]
CREATE DOMAIN Valuta AS money
	CHECK(
	      VALUE>=CAST(0 AS money)
	);

CREATE TABLE Citta(
CodiceComune	CHAR(4) PRIMARY KEY,
Nome			TEXT NOT NULL,
Provincia		CHAR(2) NOT NULL
);

CREATE TABLE Sede(
Nome 			TEXT NOT NULL UNIQUE,
Via			TEXT NOT NULL,
Latitudine		NUMERIC(8,6) NOT NULL,
Longitudine		NUMERIC(9,6) NOT NULL,
Telefono 		VARCHAR(30),
Email 		TEXT,
CodiceComune	CHAR(4) REFERENCES Citta(CodiceComune) NOT NULL,
CodiceSede		SERIAL PRIMARY KEY,
UNIQUE (Latitudine, Longitudine)
);

CREATE TABLE Sala(
Sigla			CHAR(2) NOT NULL, 
Nome			TEXT NOT NULL,
TotalePosti		INTEGER NOT NULL,
CodiceSede		INTEGER REFERENCES Sede(CodiceSede) NOT NULL,
IDSala		SERIAL PRIMARY KEY,
UNIQUE(Sigla, CodiceSede)
);

CREATE TABLE Poltrona(
IDPoltrona		SERIAL PRIMARY KEY,
Fila			CHAR(1) NOT NULL,
Numero		INTEGER NOT NULL,
Prenotabile		BOOLEAN NOT NULL,
IDSala		INTEGER REFERENCES Sala(IDSala) NOT NULL,
UNIQUE(IDSala, Fila, Numero)
);

CREATE TABLE Tecnico(
CodiceSede 		INTEGER REFERENCES Sede(CodiceSede) NOT NULL,
CodFiscale		CHAR(16) PRIMARY KEY,
Cognome		TEXT NOT NULL,
Nome			TEXT NOT NULL
);

CREATE TABLE Proiettore (
IDPrj			SERIAL PRIMARY KEY,
Seriale			INT UNIQUE NOT NULL,
Marca			TEXT NOT NULL,
Modello			TEXT NOT NULL,
IntervManutenz	INT NOT NULL, --Intervallo (massimo) tra i "tagliandi" al proiettore, in minuti
dataLampadina	TIMESTAMP WITH TIME ZONE NOT NULL,
In3D			BOOLEAN NOT NULL,
IDsala			INT REFERENCES Sala (IDsala) NOT NULL
);

CREATE TABLE InterventoManutenzione(
IDPrj			INTEGER REFERENCES Proiettore(IDPrj) NOT NULL,
NumeroPratica	SERIAL PRIMARY KEY,
CodFiscale		VARCHAR(16) REFERENCES Tecnico(CodFiscale) NOT NULL,
DataApertura	DATE NOT NULL,
DataChiusura	DATE,
Descrizione		TEXT NOT NULL
);

CREATE TABLE Evento (
Codice		SERIAL PRIMARY KEY,
Locandina 	BYTEA, -- locandina dell'evento
Titolo		TEXT NOT NULL,
Descrizione		TEXT NOT NULL,
DurataInSala	INT NOT NULL,
DurataProiezione	INT NOT NULL CHECK (DurataInSala >= DurataProiezione) 
);

CREATE TABLE Palinsesto (
Codice		SERIAL PRIMARY KEY,
DataOra		TIMESTAMP WITH TIME ZONE NOT NULL,
CodiceEvento	INT REFERENCES Evento (Codice) NOT NULL,
In3D		BOOLEAN NOT NULL ,
/*
 * Non possono esserci due voci di palinsesto che iniziano alla stessa ora
 * per lo stesso evento (a meno che uno non sia la versione 3D dell'altro)
 */
UNIQUE (DataOra, CodiceEvento, In3D) 
);

/*
COLLEGAMENTO Evento-Palinsesto-Spettacolo:
Un Evento registra le caratteristiche di un evento generico, che puo' ripetersi piu' volte.
Il Palinsesto programma le date e gli orari in cui gli eventi avranno luogo.
Lo Spettacolo indica se un evento in Palinsesto e' prenotabile e da che data.
OSS: le informazioni di Spettacolo non sono state accorpate in Palinsesto per dare modo di avere piu' spettacoli uguali alla stessa ora in sale diverse!
*/

CREATE TABLE Spettacolo ( 
Codice 		SERIAL PRIMARY KEY,
DataPrenotabile 	TIMESTAMP WITH TIME ZONE NOT NULL, 
CodicePalinsesto 	INT REFERENCES Palinsesto (Codice) NOT NULL,
Valido 		BOOLEAN NOT NULL
);

CREATE TABLE R_proiettato (
Durata 		int NOT NULL, 
CodiceSpettacolo 	int REFERENCES Spettacolo (Codice) NOT NULL,
IDPrj 		int REFERENCES Proiettore (IDPrj) NOT NULL,
IDsala 		int REFERENCES Sala (IDsala) NOT NULL,
PRIMARY KEY (CodiceSpettacolo, IDPrj, IDsala)
);

CREATE TABLE Listino (
Tipo 			TEXT PRIMARY KEY,
TotaleIngressi 	INTEGER CHECK(TotaleIngressi > 0) NOT NULL,
PrezzoListino 	Valuta NOT NULL
);

CREATE TABLE Utente (
IDUtente 		SERIAL PRIMARY KEY,
Email 		TEXT NOT NULL UNIQUE,
Password 		CHAR(32) NOT NULL,
Cognome 		TEXT NOT NULL,
Nome 			TEXT NOT NULL,
CodFiscale 		CHAR(16) NOT NULL UNIQUE,
CartaCredito 	CHAR(16),
UltimoAccesso 	TIMESTAMP WITH TIME ZONE,
Attivo 		BOOLEAN NOT NULL,
Newsletter 		BOOLEAN NOT NULL,
Amministrativo 	BOOLEAN NOT NULL,
Verificato		BOOLEAN NOT NULL
);

CREATE TABLE Abbonamento ( 
Numero 		SERIAL PRIMARY KEY,
TotaleIngressi 	INTEGER CHECK(TotaleIngressi >= 0) NOT NULL,
Prezzo 		Valuta NOT NULL,
TipoListino 	TEXT REFERENCES Listino(Tipo) NOT NULL,
IDUtente		INTEGER REFERENCES Utente(IDUtente) 
);

CREATE TABLE Tariffa (
Fascia 		TEXT PRIMARY KEY,
PrezzoListino 	Valuta NOT NULL,
In3D 			BOOLEAN NOT NULL
);

CREATE TABLE Biglietto (
NumBiglietto 	SERIAL PRIMARY KEY,
DataOraEmissione 	TIMESTAMP WITH TIME ZONE NOT NULL,
Prezzo 		Valuta NOT NULL,
IdPoltrona 		INT REFERENCES Poltrona(IdPoltrona) NOT NULL,
IdUtente 		INT REFERENCES Utente(IdUtente),
CodiceSpettacolo 	INT REFERENCES Spettacolo(Codice) NOT NULL,
Fascia 		TEXT REFERENCES Tariffa(Fascia) NOT NULL,
Pagato 		BOOLEAN NOT NULL,
Valido 		BOOLEAN NOT NULL
);

CREATE TABLE Film ( 
Codice 		INT PRIMARY KEY REFERENCES Evento(Codice),
Trama 		TEXT NOT NULL,
Genere		TEXT NOT NULL,
Regia		TEXT NOT NULL,
CastPrincipale		TEXT NOT NULL, -- la parola Cast e' riservata
UrlTrailer 		TEXT,
DataUscita 		DATE NOT NULL
);

CREATE TABLE R_Consuma ( -- Registra un record ogni volta che si emette un biglietto consumando un ingresso da un abbonamento!
NumeroBiglietto 	INTEGER REFERENCES Biglietto(NumBiglietto),
NumAbbonam 		INTEGER REFERENCES Abbonamento(Numero),
CodiceFilm 		INTEGER REFERENCES Film(Codice),
PRIMARY KEY (NumeroBiglietto, NumAbbonam, CodiceFilm)
);

CREATE TABLE Messaggio (
IDUtente 		INTEGER REFERENCES Utente(IDUtente),
DataOra 		TIMESTAMP WITH TIME ZONE,
CodiceEvento 	INTEGER REFERENCES Evento(Codice) NOT NULL,
Testo 		TEXT NOT NULL,
PRIMARY KEY (IDUtente, DataOra)
);

CREATE TABLE Fornitore (
PIVA 			VARCHAR(11) PRIMARY KEY,
Nome 			TEXT NOT NULL,
Telefono 		VARCHAR(30),
Email 		TEXT,
Url	 		TEXT,
CHECK (Telefono IS NOT NULL OR Email IS NOT NULL OR Url IS NOT NULL)
);

CREATE TABLE Noleggio (
CodiceFilm 		INT REFERENCES Film(Codice) NOT NULL,
DataInizio 		DATE NOT NULL,
DataFine 		DATE NOT NULL,
Prezzo 		Valuta NOT NULL,
PIVAFornitore 	VARCHAR(11)  REFERENCES Fornitore(PIVA) NOT NULL,
In2D 			BOOLEAN NOT NULL,
In3D 			BOOLEAN NOT NULL,
PRIMARY KEY(codiceFilm, dataInizio),
CHECK (dataFine > dataInizio),
CHECK (In2D OR In3D) -- deve esserci almeno un formato
);
\end{lstlisting}